﻿Imports System.Windows.Forms

Public Class Picker

    Public clsDBCon As New Connection
     
    Public sDatabase As String
    Public sSQL As String
    Public sField As String
    Public sReturn As String
    Public sCondition As String
    Public sPickerTitle As String

    Private nCount As Double
    Private nPerPage As Double
    Private nTotalPage As Double
    Private nToRow As Double
    Private nFromRow As Double

    Private sCond As String

    Private sDataSet As New DataSet
    Private a As New BindingSource

    Public Sub PickerDisplay(ByVal nCurrentPage As Integer, ByRef sBindSource As BindingSource)
        Try
            nCount = clsDBCon.GetValueFrTable("count(*) nCount", "(" & sSQL & IIf(sCond = "", "", " where " & sCond & sCondition) & ")a", "")
            nPerPage = My.Settings.PickerDisplayPerPage
            nTotalPage = CInt(IIf((nCount / nPerPage) > 0 And (nCount / nPerPage) < 1, 1, (nCount / nPerPage)))
            txtPageTotal.Text = nTotalPage

            nToRow = nCurrentPage * nPerPage
            nFromRow = (nToRow - nPerPage) + 1

            sDataSet.Tables.Clear()
            Dim tmpSQL As String
            tmpSQL = "select nRow," & sField & " from ( select ROW_NUMBER() OVER(ORDER BY " & sField & ") nRow,* from ( " & sSQL & IIf(sCond = "", "", " where " & sCond) & " )a )a"
            clsDBCon.ExecuteQuery(tmpSQL, sDataSet)
            a.DataSource = sDataSet.Tables(0)
            a.Filter = " (nRow >= '" & CInt(nFromRow) & "' and nRow <= '" & CInt(nToRow) & "') "
            clsDBCon.Databind(Me, a)

            If My.Settings.PickerDisplayRowNo = False Then dtgList.Columns.Remove("nRow")

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub Pick_Item()
        Try
            Dim s As String = "▪"
            If dtgList.RowCount <> 0 Then
                For i As Integer = 1 To dtgList.Columns.Count
                    If dtgList.CurrentRow.Cells.Item(i - 1).Visible = True Then
                        s = s & dtgList.CurrentRow.Cells.Item(i - 1).Value.ToString() & "▪"
                    End If
                Next
                DialogResult = Windows.Forms.DialogResult.OK
                sReturn = s
                Me.Close()
            Else
                Me.Close()
                Exit Sub
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub Picker_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With clsDBCon
            .DatabasePassword = .ConnectionPassword
            .DatabaseServer = .ConnectionServer
            .DatabaseServerType = Connection.ServerType.MSSQL
            .DatabaseUserName = .ConnectionUserName
            .DatabaseName = sDatabase
            .Connect(.SQLCn)

            Me.Text = sPickerTitle
            grpSettings.Visible = False
            PickerDisplay(txtPageNo.Text, a)

        End With
    End Sub

    Private Sub dtgList_CellContentDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dtgList.CellContentDoubleClick
        Call Pick_Item()
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        Call Pick_Item()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        txtPageNo.Text = 1
        PickerDisplay(txtPageNo.Text, a)
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        txtPageNo.Text = nTotalPage
        PickerDisplay(txtPageTotal.Text, a)
    End Sub

    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        txtPageNo.Text = IIf(Val(txtPageNo.Text) - 1 < 1, 1, Val(txtPageNo.Text) - 1)
        PickerDisplay(txtPageNo.Text, a)
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        txtPageNo.Text = IIf(Val(txtPageNo.Text) + 1 > Val(txtPageTotal.Text), txtPageTotal.Text, Val(txtPageNo.Text) + 1)
        PickerDisplay(txtPageNo.Text, a)
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim s() As String

        If txtFind.Text <> "" Then
            sCond = ""
            txtPageNo.Text = 1
            s = Split(sField, ",")
            For i As Integer = 0 To UBound(s)
                If sCond = "" Then
                    If Mid$(s(i), 1, 1) <> "n" And Mid$(s(i), 1, 1) <> "l" Then
                        sCond = sCond & s(i) & " like '" & txtFind.Text & "%'"
                    End If
                Else
                    If Mid$(s(i), 1, 1) <> "n" And Mid$(s(i), 1, 1) <> "l" Then
                        sCond = sCond & " or " & s(i) & " like '" & txtFind.Text & "%'"
                    End If
                End If
            Next
        Else
            sCond = ""
        End If
        PickerDisplay(txtPageNo.Text, a)
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        My.Settings.PickerDisplayPerPage = txtPerPage.Text
        My.Settings.PickerDisplayRowNo = chkRowNo.Checked
        My.Settings.Save()
        grpSettings.Visible = False
        MsgBox("Settings save!", MsgBoxStyle.Information, TitleText)
        txtPageNo.Text = "1"
        PickerDisplay(txtPageNo.Text, a)
    End Sub

    Private Sub btnSettings_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSettings.Click
        grpSettings.Visible = True
        txtPerPage.Text = My.Settings.PickerDisplayPerPage
        chkRowNo.Checked = My.Settings.PickerDisplayRowNo
    End Sub
End Class
